package com.vvkee.modules.system.mapper;


import com.vvkee.modules.system.entity.TableFileds;
import com.vvkee.modules.system.entity.Tables;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p> 查询表数据信息 </p>
 *
 * @author : zhengqing
 * @description :
 * @date : 2019/11/8 15:46
 */
@Mapper
public interface TableMapper {

    /**
     * 获取指定数据库下所有表名和注释
     *
     * @param dbName:数据库名
     * @return: java.util.List<com.zhengqing.demo.modules.system.entity.Tables>
     */
    @Select("SELECT DISTINCT \"table_name\" as name,obj_description ( oid, 'pg_class' ) AS comment FROM information_schema.tables t1, pg_class t2 WHERE table_schema = #{dbName} AND t1.\"table_name\" = t2.relname")
    List<Tables> getAllTables(@Param("dbName") String dbName);

    /**
     * 获取指定表信息
     *
     * @param tableName:表
     * @return: java.util.List<com.zhengqing.demo.modules.system.entity.TableFileds>
     */
    @Select("SELECT distinct ordinal_position, column_name as field ,des.description as comment, udt_name as type, is_nullable as nullabled,\n" +
            "case when udt_name ~ 'int|numeric' then numeric_precision \n" +
            "when udt_name ~ 'varchar' then character_maximum_length \n" +
            "when udt_name ~ 'timestamp' then datetime_precision else null end as file_length\n" +
            "from \n" +
            "information_schema.columns col\n" +
            "left join pg_class pgc on col.table_name = pgc.relname\n" +
            "left join pg_description des ON pgc.oid = des.objoid AND col.ordinal_position = des.objsubid\n" +
            "WHERE\n" +
            "table_name = #{tableName}\n" +
            "order by ordinal_position")
    List<TableFileds> getTable(@Param("tableName") String tableName);

}
